package main

import (
	"fmt"
	"gorm.io/driver/postgres"
	"gorm.io/gorm"
	"gorm.io/gorm/logger"
	"log"
	"os"
	"strings"
	"time"
)

var sql = `
with cte_org as (\r\n\tselect \r\n\t\torganization_id as org_id,\r\n\t\torganization_name as org_name,\r\n\t\t\r\n\t\t    org_organization.city_id as area_id\r\n    \t\r\n\tfrom \r\n\t\torg_organization \r\n\twhere \r\n\t\tdeleted_at is null \r\n\t\t\r\n\t\t    and province_id = @auths_ProvinceId\r\n\t\t\r\n),\r\ncte_veh as (\r\n\tselect \r\n\t\tveh_info.veh_id,\r\n\t\tveh_info.business_scope,\r\n\t\tcte_org.*\r\n\tfrom \r\n\t\tveh_info\r\n\tinner join\r\n\t\tcte_org\r\n\ton \r\n\t\tveh_info.deleted_at is null \r\n\t\tand veh_info.enterprise_id = cte_org.org_id\r\n),\r\ncte_area as (\r\n\tselect \r\n\t\tarea_id,\r\n\t\tname as area_name\r\n\tfrom \r\n\t\tsys_area\r\n\twhere \r\n\t\t\r\n    \t    pid = @auths_ProvinceId\r\n    \t\r\n),\r\ncte_common_business_scope as (\r\n\tselect \r\n\t\tsys_dictionary.value as business_scope,\r\n\t\tsys_dictionary.name as business_scope_name\r\n\tfrom \r\n\t\tsys_dictionary\r\n\tinner join (\r\n\t\tselect \r\n\t\t\tdictionary_category_id\r\n\t\tfrom \r\n\t\t\tsys_dictionary_category\r\n\t\twhere\r\n\t\t\tcategory_code = 'common_business_scope'\r\n\t) as temp_sys_dictionary_category\r\n\ton \r\n\t\tsys_dictionary.dictionary_category_id = temp_sys_dictionary_category.dictionary_category_id\r\n),\r\ncte_driving_log_statistics as (\r\n\tselect \r\n\t\tcte_veh.area_id,\r\n\t\tcte_veh.business_scope,\r\n\t\tdriving_log_statistics.vehicle_id,\r\n\t\tdriving_log_statistics.is_register\r\n\tfrom \r\n\t\tdriving_log_statistics\r\n\tinner join \r\n\t\tcte_veh \r\n\ton \r\n\t\tdriving_log_statistics.deleted_at is null \r\n\t\tand driving_log_statistics.created_at between '2021-05-01 08:00:46' and '2021-05-31 08:59:46'\r\n\t\tand driving_log_statistics.vehicle_id = cte_veh.veh_id\r\n),\r\ncte_count_title as (\r\n\tselect \r\n\t\t*\r\n\tfrom\r\n\t\tcte_area\r\n\tcross join\r\n\t\tcte_common_business_scope\r\n\torder by\r\n\t\tcte_area.area_id,\r\n\t\tcte_common_business_scope.business_scope\r\n),\r\ncte_count_cte_veh as (\r\n\tselect\r\n\t\tarea_id, \r\n\t\tbusiness_scope,\r\n\t\tcount(*) as total_car\r\n\tfrom \r\n\t\tcte_veh\r\n\tgroup by \r\n\t\tarea_id, \r\n\t\tbusiness_scope\r\n),\r\ncte_count_driving_log_statistics as (\r\n\tselect\r\n\t\tarea_id, \r\n\t\tbusiness_scope,\r\n\t\tcount(*) as total_driving,\r\n\t\tcount(is_register = false or null) as total_driving_no_register\r\n\tfrom \r\n\t\tcte_driving_log_statistics\r\n\tgroup by \r\n\t\tarea_id, \r\n\t\tbusiness_scope\r\n),\r\ncte_result as (\r\n\tselect \r\n\t\tcte_count_title.*,\r\n\t\tcoalesce(cte_count_cte_veh.total_car, 0) as total_car,\r\n\t\tcoalesce(cte_count_driving_log_statistics.total_driving, 0) as total_driving,\r\n\t\tcoalesce(cte_count_driving_log_statistics.total_driving_no_register, 0) as total_driving_no_register\r\n\tfrom \r\n\t\tcte_count_title\r\n\tleft join\r\n\t\tcte_count_cte_veh\r\n\ton \r\n\t\tcte_count_title.area_id = cte_count_cte_veh.area_id\r\n\t\tand cte_count_title.business_scope = cte_count_cte_veh.business_scope\r\n\tleft join\r\n\t\tcte_count_driving_log_statistics\r\n\ton \r\n\t\tcte_count_title.area_id = cte_count_driving_log_statistics.area_id\r\n\t\tand cte_count_title.business_scope = cte_count_driving_log_statistics.business_scope\r\n)\r\nselect \r\n\t*\r\nfrom\r\n\tcte_result
`

func main() {

	newLogger := logger.New(
		log.New(os.Stdout, "\r\n", log.LstdFlags), // io writer
		logger.Config{
			SlowThreshold: time.Second, // 慢 SQL 阈值
			LogLevel:      logger.Info, // Log level
			Colorful:      true,        // 禁用彩色打印
		},
	)
	fmt.Println(newLogger)
	DB, _ := gorm.Open(postgres.Open("host=192.168.3.132 user=postgres password=mysecretpassword dbname=common_report port=5445 sslmode=disable TimeZone=Asia/Shanghai"), &gorm.Config{QueryFields: true, Logger: newLogger})
	d := DB.Session(&gorm.Session{DryRun: true}).Raw(cleanSql(sql), map[string]interface{}{"created_start_time": "2020-02-03 22:22:22", "created_end_time": "2020-02-03 22:22:22", "auths_ProvinceId": "1"})
	fmt.Println(d.Dialector.Explain(d.Statement.SQL.String(), d.Statement.Vars...))

}

func cleanSql(sql string) string {
	sql = strings.ReplaceAll(sql, "\\n", "")
	sql = strings.ReplaceAll(sql, "\\r", "")
	sql = strings.ReplaceAll(sql, "\\t", "")
	return sql
}
